<?php
class Perbuatan_model extends MY_Model {
	
    function Perbuatan_model(){
    	parent::MY_Model();
        $this->table_name = "perbuatan";
        $this->table_fields = array('perb_id','perb_tgl_lapor','perb_tgl_kejadian', 'perb_tempat_kejadian',
        	'perb_keterangan','perb_status','perb_status_change','lapor_id','perb_poster');
    }
    
    function select_last_entry($search_key=false, $where=false, $limit=10, $offset=0, $orderby=""){
    	if ($search_key){
    		if (!is_array($search_key)) $search_key = array($search_key);
			
			$qq = '';
			foreach($search_key as $kk){
				foreach($this->table_fields as $ff){
					$qq .= ($qq? " OR " : "").$this->table_name.".$ff LIKE '%$kk%'";
				}
			}
			$this->where("($qq)");
    	}
    	if ($where) $this->where($where);
    	if ($orderby){
    		$this->order_by($orderby);
    	}
    	
    	return $this->get($limit, $offset);
    }
        
    function select_by_id($id){
    	return $query = $this->db->get_where($this->table_name, array('perb_id' => $id));
    }
    
	function get_last_id(){
    	$this->db->select_max($this->table_fields[0]);
    	$rr = $this->db->get($this->table_name);
    	$rr = (array)$rr->row();
    	return $rr[$this->table_fields[0]];
    }
    
    /**
     * 
     * @param $supervisorUsername
     */
    function getRequestApproval($supervisorUsername, $search_key, $where, $limit, $offset, $orderby){
		$this->select($this->table_name.".*");
		$this->join('perbuatan_detil as b', 'b.perb_id='.$this->table_name.'.perb_id');
		$this->join('karyawan_history_jabatan as c', 'c.kary_jbt_id=b.kary_jbt_id');
		$this->join('jabatan as d', 'd.jbt_id = c.jbt_id');
    	$this->where("d.jbt_supervisi IN (
    		SELECT x.jbt_id 
    		FROM karyawan_history_jabatan as x 
    		WHERE kary_id='$supervisorUsername')");
    	$this->where("perb_status='RA'");
    	if ($search_key){
    		if (!is_array($search_key)) $search_key = array($search_key);
			
			$qq = '';
			foreach($search_key as $kk){
				foreach($this->table_fields as $ff){
					$qq .= ($qq? " OR " : "").$this->table_name.".$ff LIKE '%$kk%'";
				}
			}
			$this->where("($qq)");
    	}
    	if ($where) $this->where($where);
    	if ($orderby){
    		$this->order_by($orderby);
    	}
    	
    	$this->group_by('perb_id');
		return $this->get($limit, $offset);
    }
    
 /**
     * 
     * @param $supervisorUsername
     */
    function getApproved($posterUsrName, $search_key, $where, $limit, $offset, $orderby){
		$this->select($this->table_name.".*");
		$this->join('perbuatan_detil as b', 'b.perb_id='.$this->table_name.'.perb_id');
		$this->join('karyawan_history_jabatan as c', 'c.kary_jbt_id=b.kary_jbt_id');
		
		//untuk request ttd
		$this->join('bap as e', 'e.perb_id='.$this->table_name.'.perb_id');
		$this->join('bap_sign as f', 'f.bap_id=e.bap_id');
		
    	$this->where("(perb_poster=MD5('$posterUsrName') AND perb_status='AP')");
    	$this->or_where("(f.kary_id='$posterUsrName' AND f.bap_sign_type='WT' AND perb_status='AP')");
    	if ($search_key){
    		if (!is_array($search_key)) $search_key = array($search_key);
			
			$qq = '';
			foreach($search_key as $kk){
				foreach($this->table_fields as $ff){
					$qq .= ($qq? " OR " : "").$this->table_name.".$ff LIKE '%$kk%'";
				}
			}
			$this->where("($qq)");
    	}
    	if ($where) $this->where($where);
    	if ($orderby){
    		$this->order_by($orderby);
    	}
    	
    	$this->group_by('perb_id');
		return $this->get($limit, $offset);
    }

	function getRejected($posterUsrName, $search_key, $where, $limit, $offset, $orderby){
		/**
    	 * SQL:
    	 * SELECT a.perb_id, b.*, c.*,d.* FROM `perbuatan` as a
    	 * 	JOIN perbuatan_detil as b ON b.perb_id = a.perb_id
    	 * 	JOIN karyawan_history_jabatan as c ON c.kary_jbt_id = b.kary_jbt_id
    	 * WHERE perb_poster=MD5('02410100257')
    	 * 		AND perb_status='RE'
    	 * )
    	 */
		$this->select($this->table_name.".*");
		$this->join('perbuatan_detil as b', 'b.perb_id='.$this->table_name.'.perb_id');
		$this->join('karyawan_history_jabatan as c', 'c.kary_jbt_id=b.kary_jbt_id');
    	$this->where("perb_poster=MD5('$posterUsrName')");
    	$this->where("perb_status='RE'");
    	if ($search_key){
    		if (!is_array($search_key)) $search_key = array($search_key);
			
			$qq = '';
			foreach($search_key as $kk){
				foreach($this->table_fields as $ff){
					$qq .= ($qq? " OR " : "").$this->table_name.".$ff LIKE '%$kk%'";
				}
			}
			$this->where("($qq)");
    	}
    	if ($where) $this->where($where);
    	if ($orderby){
    		$this->order_by($orderby);
    	}
    	
    	$this->group_by('perb_id');
		return $this->get($limit, $offset);
	}
}